﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace Model
{
    public static class StaticUtil
    {
        #region getvalue
        public static bool GetBool(object value)
        {
            return GetBool(value, false);
        }

        public static bool GetBool(object value, bool valueWhenNull)
        {
            if (value == null)
                return valueWhenNull;
            else
                if (value is DBNull)
                    return valueWhenNull;
                else
                    return (bool)value;
        }

        public static bool GetBool(bool? value)
        {
            return GetBool(value, false);
        }

        public static bool GetBool(bool? value, bool valueWhenNull)
        {
            if (value == null)
                return valueWhenNull;
            else
                return value.Value;
        }

        public static bool? GetRealBool(object value)
        {
            if (value == null)
                return null;
            else
                if (value is DBNull)
                    return null;
                else
                    return Convert.ToBoolean(value);
        }

        public static int GetInt(object value)
        {
            return GetInt(value, int.MinValue);
        }

        public static int GetInt(object value, int valueWhenNull)
        {
            if (value == null)
                return valueWhenNull;
            if (value is DBNull)
                return valueWhenNull;

            int i = 0;
            try
            {
                i = Convert.ToInt32(value);
            }
            catch
            {
                return valueWhenNull;
            }
            return i;
        }

        public static int GetInt(int? value)
        {
            return GetInt(value, int.MinValue);
        }

        public static int GetInt(int? value, int valueWhenNull)
        {
            if (value == null)
                return valueWhenNull;
            else
                return value.Value;
        }

        public static int? GetRealInt(object value)
        {
            return GetRealInt(value, null);
        }

        public static int? GetRealInt(object value, int? valueWhenNull)
        {
            if (value == null)
                return valueWhenNull;
            if (value is DBNull)
                return valueWhenNull;
            int i = 0;
            try
            {
                i = Convert.ToInt32(value);
            }
            catch
            {
                return null;
            }
            return i;
        }

        public static double GetDouble(double? value)
        {
            return GetDouble(value, double.MinValue);
        }

        public static double GetDouble(object value)
        {
            return GetDouble(value, double.MinValue);
        }

        public static double? GetRealDouble(object value)
        {
            if (value == null)
                return null;
            if (value is DBNull)
                return null;
            return GetDouble(value);
        }

        public static double GetDouble(double? value, double valueWhenNull)
        {
            if (value == null)
                return valueWhenNull;
            else
                if (double.IsInfinity(value.Value))
                    return valueWhenNull;
                else
                    return value.Value;
        }

        public static double GetDouble(object value, double valueWhenNull)
        {
            double d = valueWhenNull;
            if (value == null)
                return d;
            if (value is DBNull)
                return d;

            System.Globalization.NumberFormatInfo format = new System.Globalization.NumberFormatInfo();
            format.NumberGroupSeparator = ",";
            format.NumberDecimalSeparator = ".";

            string svalue = value.ToString();
            svalue = svalue.Replace(',', '.');

            try
            {
                d = double.Parse(svalue, format);
            }
            catch
            {
            }

            return d;
        }

        public static DateTime GetDateTime(object value, DateTime valueWhenNull)
        {
            if (value == null)
                return valueWhenNull;
            if (value is DBNull)
                return valueWhenNull;

            DateTime i = DateTime.MinValue;
            try
            {
                i = Convert.ToDateTime(value);
            }
            catch
            {
                return valueWhenNull;
            }
            return i;
        }

        public static DateTime GetDateTime(object value)
        {
            return GetDateTime(value, DateTime.MinValue);
        }

        public static DateTime GetDateTime(DateTime? value)
        {
            return GetDateTime(value, DateTime.MinValue);
        }

        public static DateTime GetDateTime(DateTime? value, DateTime valueWhenNull)
        {
            if (value == null)
                return valueWhenNull;
            else
                return value.Value;
        }

        public static DateTime? GetRealDateTime(object value)
        {
            if (value == null)
                return null;
            if (value is DBNull)
                return null;
            return Convert.ToDateTime(value);
        }

        public static string GetTextDateTime(DateTime? dt)
        {
            if (dt == null)
                return null;
            else
                return dt.Value.ToString("dd/MM/yyyy");
        }
        #endregion getvalue

        #region other
        public static string[] GetDanhMucChung(DanhMucChung loaiDanhMuc)
        {
            switch (loaiDanhMuc)
            {
                case DanhMucChung.DiaPhuong:
                    return new string[] { "DiaPhuong", "địa phương" };
                case DanhMucChung.NguonGoc:
                    return new string[] { "NguonGoc", "nguồn gốc" };
                case DanhMucChung.LoaiVanBan:
                    return new string[] { "LoaiVanBan", "hệ thống văn bản áp dụng" };
                case DanhMucChung.VBGiayToSDD:
                    return new string[] { "GiayToSDD", "văn bản, giấy tờ sử dụng đất" };
                case DanhMucChung.TenDat:
                    return new string[] { "TenDat", "tên đất" };
                case DanhMucChung.DuongPho:
                    return new string[] { "DuongPho", "đường phố" };
                default:
                    return new string[] { "", "" };
            }
        }

        public static string GetLoaiDanhMucChung(DanhMucChung loaiDanhMuc)
        {
            return GetDanhMucChung(loaiDanhMuc)[0];
        }

        public static string[] GetDanhMucHeSo(DanhMucHeSo loaiDanhMuc)
        {
            switch (loaiDanhMuc)
            {
                case DanhMucHeSo.HeSoCuLy:
                    return new string[] { "HeSoCuLy", "hệ số cự ly" };
                case DanhMucHeSo.HeSoHaTangCoSo:
                    return new string[] { "HeSoHaTangCoSo", "hệ số hạ tầng cơ sở" };
                case DanhMucHeSo.HeSoViTriDacBiet:
                    return new string[] { "HeSoViTriDacBiet", "hệ số vị trí đặc biệt" };
                case DanhMucHeSo.TyLeGiamGia:
                    return new string[] { "TyLeGiamGia", "tỷ lệ giảm giá" };
                case DanhMucHeSo.LoaiDienTich:
                    return new string[] { "LoaiDienTich", "loại diện tích" };
                case DanhMucHeSo.HeSoTangNha:
                    return new string[] { "HeSoTangNha", "hệ số tầng nhà" };
                case DanhMucHeSo.MaNha_KetCau:
                    return new string[] { "MaNha_KetCau", "mã nhà, kết cấu" };
                default:
                    return new string[] { "", "hệ số" };
            }
        }

        public static string GetLoaiDanhMucHeSo(DanhMucHeSo loaiDanhMuc)
        {
            return GetDanhMucHeSo(loaiDanhMuc)[0];
        }

        public static string RebuiltHoTen(string s)
        {
            if (string.IsNullOrEmpty(s))
                return s;
            else
            {
                s = s.Trim();
                while (s.IndexOf("  ") >= 0)
                    s = s.Replace("  ", " ");

                string[] arrS = s.Split(' ');
                s = "";
                int i = 0;
                for (i = 0; i < arrS.Length; i++)
                {
                    if (!string.IsNullOrEmpty(arrS[i]))
                    {
                        s += (s == "" ? "" : " ");
                        if (arrS[i].Length == 1)
                            s += arrS[i].ToUpper();
                        else
                            s += arrS[i].Substring(0, 1).ToUpper() + arrS[i].Substring(1).ToLower();
                    }
                }
            }

            return s;
        }

        public static string GetFilenameOnly(string s)
        {
            if (string.IsNullOrEmpty(s))
                return "";
            else
            {
                int i = s.LastIndexOf("\\");
                s = s.Substring(i + 1);
                return s;
            }
        }

        public static string UpperFirstCharacter(string s)
        {
            s = s.ToLower().Trim();
            string s1 = s.Substring(0, 1).ToUpper();
            s = s.Remove(0, 1);
            s = s.Insert(0, s1);
            return s;
        }
        #endregion other

        #region Database
        public static void AttactDB(string mdfFile, string dbname)
        {
            string DBConnectionStr = "Data Source=" + Session.SqlDataSource + ";Initial Catalog=master;Trusted_Connection=true;";

            try
            {
                using (SqlConnection cnn = new SqlConnection(DBConnectionStr))
                {
                    cnn.Open();
                    string scmd = "sp_attach_single_file_db @dbname = '" + dbname + "', @physname = N'" + mdfFile + "'";
                    SqlCommand cmd = new SqlCommand(scmd, cnn);
                    cmd.ExecuteNonQuery();

                    scmd = "if not exists (select name from master.sys.databases sd where name = N'" + dbname + "' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [" + dbname + "].dbo.sp_changedbowner @loginame=N'" + Session.SqlUserID + "', @map=false";
                    cmd.CommandText = scmd;
                    cmd.ExecuteNonQuery();

                    cnn.Close();
                    cnn.Dispose();
                }
            }
            catch { throw; }
        }

        public static void AttactDB(string sourceFile, string descPath, string dbname)
        {
            string DBConnectionStr = "Data Source=" + Session.SqlDataSource + ";Initial Catalog=master;Trusted_Connection=true;";

            try
            {
                File.Copy(sourceFile, descPath + dbname + ".mdf", true);

                using (SqlConnection cnn = new SqlConnection(DBConnectionStr))
                {
                    cnn.Open();
                    string scmd = "sp_attach_single_file_db @dbname = '" + dbname + "', @physname = N'" + descPath + dbname + ".mdf'";
                    SqlCommand cmd = new SqlCommand(scmd, cnn);
                    cmd.ExecuteNonQuery();

                    scmd = "if not exists (select name from master.sys.databases sd where name = N'" + dbname + "' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [" + dbname + "].dbo.sp_changedbowner @loginame=N'" + Session.SqlUserID + "', @map=false";
                    cmd.CommandText = scmd;
                    cmd.ExecuteNonQuery();

                    cnn.Close();
                    cnn.Dispose();
                }
            }
            catch { throw; }
        }

        public static void DetactDB(string dbname)
        {
            string DBConnectionStr = "Data Source=" + Session.SqlDataSource + ";Initial Catalog=master;Trusted_Connection=true;";

            try
            {
                using (SqlConnection cnn = new SqlConnection(DBConnectionStr))
                {
                    cnn.Open();
                    string scmd = "DECLARE @SQL varchar(max)\r\nSET @SQL = ''\r\nSELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';'\r\nFROM MASTER..SysProcesses\r\nWHERE DBId = DB_ID(N'" + dbname + "') AND SPId <> @@SPId\r\nEXEC(@SQL)";

                    using (SqlCommand cmd = new SqlCommand(scmd, cnn))
                    {
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "EXEC master.dbo.sp_detach_db @dbname = N'" + dbname + "', @keepfulltextindexfile=N'true'";
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    }

                    cnn.Close();
                    cnn.Dispose();
                }
            }
            catch { throw; }
        }

        public static void CreateSqlUserID(string username, string password)
        {
            string DBConnectionStr = "Data Source=" + Session.SqlDataSource + ";Initial Catalog=master;Trusted_Connection=true;";

            try
            {
                using (SqlConnection cnnGetAllDB = new SqlConnection(DBConnectionStr))
                {
                    cnnGetAllDB.Open();

                    bool hasDBUSR = false;
                    using (SqlCommand cmdGetAllLogins = new SqlCommand("SELECT * FROM syslogins WHERE name=N'" + username + "'", cnnGetAllDB))
                    {
                        using (SqlDataReader reader = cmdGetAllLogins.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                string loginsName = (string)reader["name"];
                                if (loginsName.ToUpper() == username.ToUpper())
                                    hasDBUSR = true;
                            }

                            reader.Close();
                            reader.Dispose();
                        }

                        cmdGetAllLogins.Dispose();
                    }

                    if (hasDBUSR == false)
                    {
                        using (SqlCommand cmdAddDBUSR = new SqlCommand("CREATE LOGIN [" + username + "] WITH PASSWORD=N'" + password + "', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF", cnnGetAllDB))
                        {
                            cmdAddDBUSR.ExecuteNonQuery();
                            cmdAddDBUSR.Dispose();
                        }

                        using (SqlCommand cmdSetAdminRole = new SqlCommand("EXEC sys.sp_addsrvrolemember @loginame = N'" + username + "', @rolename = N'sysadmin'", cnnGetAllDB))
                        {
                            cmdSetAdminRole.ExecuteNonQuery();
                            cmdSetAdminRole.Dispose();
                        }
                    }

                    cnnGetAllDB.Close();
                    cnnGetAllDB.Dispose();
                }
            }
            catch { throw; }
        }

        public static bool CheckDB(string DBname)
        {
            string DBConnectionStr = "Data Source=" + Session.SqlDataSource + ";Initial Catalog=master;Trusted_Connection=true;";
            bool isExist = false;

            try
            {
                using (SqlConnection cnnGetAllDB = new SqlConnection(DBConnectionStr))
                {
                    cnnGetAllDB.Open();

                    using (SqlCommand cmdGetAllDB = new SqlCommand("SELECT name AS DBName FROM sys.databases", cnnGetAllDB))
                    {
                        using (SqlDataReader reader = cmdGetAllDB.ExecuteReader())
                        {
                            string name = "";
                            DBname = DBname.ToUpper();

                            while (reader.Read())
                            {
                                name = (string)reader[0];
                                if (name.ToUpper() == DBname)
                                {
                                    isExist = true;
                                    break;
                                }
                            }

                            reader.Close();
                            reader.Dispose();
                        }

                        cmdGetAllDB.Dispose();
                    }

                    cnnGetAllDB.Close();
                    cnnGetAllDB.Dispose();
                }
            }
            catch { throw; }

            return isExist;
        }

        public static object[] GetInfoDB(string dbname, bool getLockFile)
        {
            //return: real path; size (bytes); log_file
            string scmd = "SELECT DB_NAME(mf.database_id) AS DatabaseName,mf.physical_name AS physical_name,size_on_disk_bytes FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id AND mf.file_id = divfs.file_id AND DB_NAME(mf.database_id)=N'" + dbname + "'";

            if (getLockFile == false)
                scmd += " AND mf.physical_name LIKE '%.mdf'";

            scmd += " ORDER BY DatabaseName";
            object[] result = new object[3];

            string DBConnectionStr = "Data Source=" + Session.SqlDataSource + ";Initial Catalog=master;Trusted_Connection=true;";

            try
            {
                using (SqlConnection cnn = new SqlConnection(DBConnectionStr))
                {
                    cnn.Open();

                    using (SqlCommand cmd = new SqlCommand(scmd, cnn))
                    {
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            string physical_name = "";

                            while (reader.Read())
                            {
                                physical_name = (string)reader["physical_name"];

                                if (physical_name.ToLower().EndsWith(".mdf"))
                                {
                                    result[0] = physical_name;
                                    result[1] = CalSizeOnBytes(reader["size_on_disk_bytes"]);
                                }
                                else
                                    if (physical_name.ToLower().EndsWith(".ldf"))
                                        result[2] = physical_name;
                            }

                            reader.Close();
                            reader.Dispose();
                        }

                        cmd.Dispose();
                    }

                    cnn.Close();
                    cnn.Dispose();
                }
            }
            catch { throw; }

            return result;
        }

        private static string CalSizeOnBytes(object size)
        {
            string[] arrTrailer = new string[] { "byte", "KB", "MB", "GB", "TB" };
            long iSize = Convert.ToInt64(size);
            int i = 0;
            long valueDIV = 1;

            while (iSize / valueDIV >= 1024)
            {
                valueDIV *= 1024;
                i++;
                if (i == 4)
                    break;
            }

            if (iSize / valueDIV < 1 && i > 0)
            {
                valueDIV /= 2024;
                i--;
            }

            long mod = iSize % valueDIV;

            return (mod == 0L ? (iSize / valueDIV).ToString() : Math.Round((decimal)(iSize / (1.0 * valueDIV)), 1, MidpointRounding.AwayFromZero).ToString()) + " " + arrTrailer[i];
        }
        #endregion Database

        #region excel process
        public static Hashtable GetExcelProcess()
        {
            Hashtable hstList = new Hashtable();

            Process[] prg = Process.GetProcesses();
            int i = 0;
            for (i = 0; i < prg.Length; i++)
            {
                if (prg[i].ProcessName.ToUpper() == "EXCEL")
                    hstList.Add(prg[i].Id, true);
            }

            return hstList;
        }

        public static void CloseMyExcel(Hashtable hstList)
        {
            Process[] prg = Process.GetProcesses();
            int i = 0;
            for (i = prg.Length - 1; i >= 0; i--)
            {
                if (hstList.ContainsKey(prg[i].Id))
                {
                    try
                    {
                        prg[i].Kill();
                    }
                    catch { }
                }
            }
        }
        #endregion excel process
    }
}
